﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using RTSoft.Modules;

namespace RTSoft.SoureCode.Control.NhanVien
{
    class nhanvienctrl
    {
        DataProvider provider = new DataProvider();
        SqlCommand cmd = new SqlCommand();
        SqlDataReader reader;
        String strSQL = "";

        public int getID(ref String errConnect, ref String errSQL, String Code)
        {
            int id = 0;
             try
            {
                cmd.Connection = provider.Connection(ref errConnect);           
                strSQL = "select ID_NV from NHANVIEN where TEN_NV = @CODE ";
                cmd.CommandText = strSQL;
                cmd.Parameters.AddWithValue("@CODE", Code);
                reader = cmd.ExecuteReader();
                if (reader.Read()) {
                    id = Convert.ToInt32(reader[0]); 
                }
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
            }
            finally
            {
                provider.Disconnect();
            }
             return id;
        }


        public void getData(ref String errConnect, ref String errSQL, ref DataTable tbl)
        {
            provider.Connection(ref errConnect);
            try
            {
                tbl = new DataTable();
                strSQL = "SELECT [ID_NV],[ID_NHOM],[ID_BAC],[TEN_NV],[USERNAME],[PASSWORD],[GIOITINH],TINH,HUYEN,[DIACHI_NV],[SDT_NV],[TRANGTHAI],[NGAY_TAO] FROM [NHANVIEN] ";
                tbl = provider.LayDanhSach(strSQL);
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
            }
            finally
            {
                provider.Disconnect();
            }
        }

        public void Insert(ref String errConnect, ref String errSQL, nhanvieninfo info)
        {
            cmd.Connection = provider.Connection(ref errConnect);
            if (errConnect != "")
            {
                return;
            }
            cmd.Transaction = provider.BeginTransaction();
            try
            {
                SqlParameter f = new SqlParameter();
                f.IsNullable = true;
                f.Value = System.DBNull.Value;
                strSQL = "Insert into NHANVIEN";
                strSQL = strSQL + "(ID_NHOM,ID_BAC,TEN_NV,USERNAME,PASSWORD,GIOITINH,TINH,HUYEN,DIACHI_NV,SDT_NV,TRANGTHAI,NGAY_TAO)";
                strSQL = strSQL + " values";
                strSQL = strSQL + "(@ID_NHOM,@ID_BAC,@TEN_NV,@USERNAME,@PASSWORD,@GIOITINH,@TINH,@HUYEN,@DIACHI_NV,@SDT_NV,@TRANGTHAI,@NGAY_TAO)";
                cmd.CommandText = strSQL;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@ID_NHOM", info.ID_NHOM);
                cmd.Parameters.AddWithValue("@ID_BAC", info.ID_BAC);
                cmd.Parameters.AddWithValue("@TEN_NV", info.TEN_NV);
                cmd.Parameters.AddWithValue("@USERNAME", info.USERNAME);
                cmd.Parameters.AddWithValue("@PASSWORD", info.PASSWORD);
                cmd.Parameters.AddWithValue("@GIOITINH", info.GIOITINH);
                cmd.Parameters.AddWithValue("@TINH", info.TINH);
                cmd.Parameters.AddWithValue("@HUYEN", info.HUYEN);
                cmd.Parameters.AddWithValue("@DIACHI_NV", info.DIACHI_NV);
                cmd.Parameters.AddWithValue("@SDT_NV", info.SDT_NV);
                cmd.Parameters.AddWithValue("@TRANGTHAI", info.TRANGTHAI);
                cmd.Parameters.AddWithValue("@NGAY_TAO", info.NGAY_TAO);
                cmd.ExecuteNonQuery();
                provider.CommitTransaction();
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
                provider.RollbackTransaction();
                //' Neu xay ra loi thi rollback lai
            }
            finally
            {
                //'Dong ket noi
                cmd.Connection.Close();
                cmd.Connection.Dispose();
            }

        }

        public void Update(ref String errConnect, ref String errSQL, nhanvieninfo info)
        {
            cmd.Connection = provider.Connection(ref errConnect);
            if (errConnect != "")
            {
                return;
            }
            cmd.Transaction = provider.BeginTransaction();
            try
            {
                SqlParameter f = new SqlParameter();
                f.IsNullable = true;
                f.Value = System.DBNull.Value;
                strSQL = "Update NHANVIEN SET  ";
                strSQL = strSQL + "ID_NHOM=@ID_NHOM,ID_BAC=@ID_BAC,TEN_NV=@TEN_NV,USERNAME=@USERNAME,PASSWORD=@PASSWORD,GIOITINH=@GIOITINH,TINH=@TINH,HUYEN=@HUYEN,DIACHI_NV=@DIACHI_NV,SDT_NV=@SDT_NV,TRANGTHAI=@TRANGTHAI,NGAY_TAO=@NGAY_TAO Where  ID_NV = @ID_NV";
                cmd.CommandText = strSQL;

                cmd.Parameters.AddWithValue("@ID_NV", info.ID_NV);
                cmd.Parameters.AddWithValue("@ID_NHOM", info.ID_NHOM);
                cmd.Parameters.AddWithValue("@ID_BAC", info.ID_BAC);
                cmd.Parameters.AddWithValue("@TEN_NV", info.TEN_NV);
                cmd.Parameters.AddWithValue("@USERNAME", info.USERNAME);
                cmd.Parameters.AddWithValue("@PASSWORD", info.PASSWORD);
                cmd.Parameters.AddWithValue("@GIOITINH", info.GIOITINH);
                cmd.Parameters.AddWithValue("@TINH", info.TINH);
                cmd.Parameters.AddWithValue("@HUYEN", info.HUYEN);
                cmd.Parameters.AddWithValue("@DIACHI_NV", info.DIACHI_NV);
                cmd.Parameters.AddWithValue("@SDT_NV", info.SDT_NV);
                cmd.Parameters.AddWithValue("@TRANGTHAI", info.TRANGTHAI);
                cmd.Parameters.AddWithValue("@NGAY_TAO", info.NGAY_TAO);
                cmd.ExecuteNonQuery();
                provider.CommitTransaction();
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
                provider.RollbackTransaction();
                //' Neu xay ra loi thi rollback lai
            }
            finally
            {
                //'Dong ket noi
                cmd.Connection.Close();
                cmd.Connection.Dispose();
            }

        }

        public void Delete(ref String errConnect, ref String errSQL, int id)
        {
            cmd.Connection = provider.Connection(ref errConnect);
            if (errConnect != "")
            {
                return;
            }
            cmd.Transaction = provider.BeginTransaction();
            try
            {
                strSQL = "DELETE FROM NHANVIEN WHERE ID_NV=@RowID";
                cmd.Parameters.AddWithValue("@RowID", id);
                cmd.CommandText = strSQL;
                cmd.ExecuteNonQuery();
                provider.CommitTransaction();
                //' Thuc hien transaction 
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
                provider.RollbackTransaction();
                //' Neu xay ra loi thi rollback lai
            }
            finally
            {
                //'Dong ket noi
                cmd.Connection.Close();
                cmd.Connection.Dispose();
            }
        }
    }
}
